import pymysql
from dbutils.pooled_db import PooledDB
import settings
import os
import sys

sys.path.append("..")
import app


# 获取连接池
def get_pool():
    pool = PooledDB(
        pymysql,
        settings.mysql_max_connections,
        host=settings.mysql_host,
        user=settings.mysql_user,
        port=settings.mysql_port,
        passwd=settings.mysql_password,
        db=settings.mysql_db,
        use_unicode=True)
    return pool


# 获取连接
def get_conn():
    return get_pool().connection()


# 根据my_flyway的配置初始化数据库
def init_db():
    if not settings.my_flyway_enable:
        return

    pool = get_pool()
    conn = pool.connection()
    cur = conn.cursor()

    cur.execute("SHOW TABLES LIKE 'my_flyway_info_table'")
    if cur.fetchone() is None:
        cur.execute("""create table if not exists my_flyway_info_table(
                            id int(4) primary key not null auto_increment,
                            sql_file_name varchar(64) not null,
                            is_execute int(1) not null default 1,
                            create_date datetime not null default CURRENT_TIMESTAMP
                            );""")
        conn.commit()
        flyway_sql = get_all_flyway_sql()
        start_flyway(cur, flyway_sql)
    else:
        flyway_sql = diff_flyway_sql(cur)
        start_flyway(cur, flyway_sql)
    # 提交数据
    conn.commit()
    cur.close()
    conn.close()


# 初始化数据库
def start_flyway(cur, sql_list):
    finish_files = []
    for file in sql_list:
        # 获取并执行sql文件中的每个语句
        is_execute = True
        file_path = os.path.join(settings.my_flyway_db_file, file)
        fd = open(file_path, 'r', encoding='utf-8')
        sql_file = fd.read()
        fd.close()
        sql_commands = sql_file.split(';')
        for command in sql_commands:
            if command.strip() == "":
                continue
            try:
                cur.execute(command)
            except Exception as msg:
                is_execute = False
                app.App().log.error("执行文件["+file+"]时出现错误----错误详情: " + msg)
        if is_execute:
            field_tuple = (file,)
            finish_files.append(field_tuple)
            app.App().log.info("flyway提示信息---- ["+file+"]文件执行成功")
    cur.executemany("insert into my_flyway_info_table(sql_file_name) values(%s)", finish_files)


# 获取本地所有sql文件
def get_all_flyway_sql():
    all_sql = os.listdir(settings.my_flyway_db_file)
    all_sql = sorted(all_sql, key=lambda x: os.path.getmtime(os.path.join(settings.my_flyway_db_file, x)))
    for sql in all_sql:
        if ".sql" not in sql:
            all_sql.remove(sql)
    return all_sql


# 检测数据库中的flyway和本地的sql不同之处
def diff_flyway_sql(cur):
    file_sql = get_all_flyway_sql()
    cur.execute("select sql_file_name from my_flyway_info_table")
    db_sql = cur.fetchall()
    db_sql_list = []
    for sql in db_sql:
        db_sql_list.append(sql[0])

    res_sql = []
    for file in file_sql:
        if file not in db_sql_list:
            res_sql.append(file)
    return res_sql
